package dao;

import db.DbClose;
import db.DbConn;
import entity.Gsales;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * 数据库gsale表的操作
 * @author hy
 * @data 2017/10/18
 */
public final class GsalesDao {

    Connection conn = null;
    PreparedStatement psmt = null;
    ResultSet rs = null;

    /**
     * 当天卖出的商品
     * @return
     */
    public ArrayList<Gsales> dailyGsales(){
        ArrayList<Gsales> gsalesArrayList = new ArrayList<Gsales>();
        conn = DbConn.getconn();
        String sql = "select gname,gprice,gnum, allSum from goods, (select gid as salesid,sum(snum) as allSum from gsales where trunc(sdate) =trunc(sysdate) group by gid) where gid = salesid";

        try{
            psmt = conn.prepareStatement(sql);
            rs = psmt.executeQuery();
            while (rs.next()){
                String gName = rs.getString(1);
                double gPrice = rs.getDouble(2);
                int gNum = rs.getInt(3);
                int allSum = rs.getInt("allsum");

                Gsales gsales = new Gsales(gName,gPrice,gNum,allSum);
                gsalesArrayList.add(gsales);
            }

        }catch (Exception e){
            e.printStackTrace();
        }
        return gsalesArrayList;
    }


    public boolean shoppingSettlement(Gsales gsales){

        boolean bool =false;
        conn = DbConn.getconn();
        String sql = "insert into gsales(GID,SID,SNUM) values(?,?,?)";

        try{
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1,gsales.getgId());
            psmt.setInt(2,gsales.getsId());
            psmt.setInt(3,gsales.getsNum());
            int rs = psmt.executeUpdate();
            if(rs>0){
                bool = true;

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbClose.addClose(psmt,conn);
        }

        return bool;
    }
}
